package com.fsbm.ctech.maxis.ebpsbackend;

import java.io.*;
import java.text.*;
import java.util.*;
import java.util.Date;
import java.sql.*;
/**
 *
 */
public class GetBillReprint {
    static ConfigObject cfgObj = new ConfigObject();

    public static Vector retrieveStatementIds(String mailMethod) throws SQLException {
        Vector reprintStatements = new Vector();
        String url                = cfgObj.databaseSourceName;
        ResultSet rs              = null;
        PreparedStatement ps      = null;
        Connection con            = null;
        Statement stmt            = null;
        String concatValue        = "";

        try {
            Class.forName(cfgObj.databaseDriver);
        } catch(java.lang.ClassNotFoundException e) {
            System.err.print("ClassNotFoundException: ");
            System.err.println(e.getMessage());
        }

        try {
            con = DriverManager.getConnection(url, cfgObj.databaseUserID, cfgObj.databasePassword);
            con.setAutoCommit(false);
            stmt = con.createStatement();

            ResultSet uprs = stmt.executeQuery("SELECT activityid, userfield1 FROM custlog WHERE activitycode = 'AC023' AND status = 'REQUEST' AND userfield2 = '" + mailMethod + "'");

            while (uprs.next()) {
                String stmtId = uprs.getString(2);
                if (!stmtId.equals("")) {
                    String activityId = uprs.getString(1);
                    concatValue = stmtId.trim() + "~" + activityId.trim();
                    reprintStatements.addElement(concatValue);
                } else {
                    cfgObj.write("Process Name(retrieveStatementIds)- Some request are invalid for Bill Reprint");
                }
            }
            uprs.close();
            stmt.close();

            // the ffg lines are temporary and needs to be incorporated inside the while loop on top to balance extracted
            con.setAutoCommit(false);
            stmt = con.createStatement();

            String sqlStmt = "UPDATE custlog SET status ='GENERATED' WHERE activityCode = 'AC023' AND status = 'REQUEST' AND userfield2 = '" + mailMethod + "'";

            stmt.executeUpdate(sqlStmt);
            con.commit();
            con.setAutoCommit(true);
            stmt.close();
            con.close();

        } catch(SQLException ex) {
            cfgObj.write("Process Name(retrieveStatementIds)- SQLSTATE: " + ex.getSQLState() +
            " Msg: " + ex.getMessage() + " ErrCode: " + ex.getErrorCode(), "reprintErr");
        } catch (Exception ex) {
            cfgObj.write("Process Name(retrieveStatementIds)- " + ex.getClass() + " " + ex.getMessage(), "reprintErr");
        }
        return reprintStatements;
    }
}

